GO
CREATE DATABASE NLLDatabase
 --drop DATABASE NLLDatabase
GO
USE NLLDatabase

GO
CREATE TABLE [Administrator](
	ID int IDENTITY(1,1) NOT NULL,
	Username nvarchar(50) NOT NULL,
	[Password] nvarchar(MAX) NOT NULL,
	[Role] nvarchar(50) NOT NULL,
	FullName nvarchar(50) NULL,
	Phone nvarchar(50) NULL,
	Email nvarchar(MAX) NOT NULL,
	[Status] bit NOT NULL,
	CONSTRAINT PK_Administrator PRIMARY KEY (ID)
)
GO
INSERT INTO [Administrator]
           ([Username]
           ,[Password]
           ,[Role]
           ,[FullName]
           ,[Phone]
           ,[Email]
           ,[Status])
     VALUES
           ('admin'
           ,'e10adc3949ba59abbe56e057f20f883e'
           ,'Administrator'
           ,'Duy Hung'
           ,'12344'
           ,'hung@gmail.com'
           ,'True')

GO 
CREATE TABLE [Menu](
	ID int IDENTITY(1,1) NOT NULL,
	Name nvarchar(255) NOT NULL,
	ParrentID int NOT NULL,
	Position nvarchar(50) NOT NULL,
	SortNumber int NOT NULL,
	[Status] bit NOT NULL,
	MetaTitle nvarchar(255) NULL,
	MetaDescription ntext NULL,
	MetaKeywords ntext NULL,
	MetaRobots nvarchar(255) NULL,
	MetaAuthor nvarchar(255) NULL,
	CONSTRAINT PK_Menu PRIMARY KEY (ID)
)

GO
CREATE TABLE Author(
	ID int IDENTITY(1,1) NOT NULL,
	AuthorName nvarchar(50) NOT NULL,
	Description ntext NULL,
	CONSTRAINT PK_Author PRIMARY KEY (ID)
)
GO
CREATE TABLE Article(
	ID int IDENTITY(1,1) NOT NULL,
	MenuID int NOT NULL,
	Title nvarchar(255) NOT NULL,
	ThumbImage ntext NOT NULL,
	[Description] ntext NOT NULL,
	Content ntext NOT NULL,
	CreateDate datetime NOT NULL,
	IsSilde bit NOT NULL,
	IsFrontPage bit NOT NULL,
	AuthorID int NULL,
	MetaTitle nvarchar(255) NULL,
	MetaDescription ntext NULL,
	MetaKeywords ntext NULL,
	MetaRobots nvarchar(255) NULL,
	MetaAuthor nvarchar(255) NULL,
	CONSTRAINT PK_Article PRIMARY KEY (ID)
)

GO

CREATE TABLE Advertise(
	ID int IDENTITY(1,1) NOT NULL,
	[Name] nvarchar(50) NOT NULL,
	Content ntext NULL,
	Position nvarchar(50) NOT NULL,
	IsFrontPage bit NOT NULL,
	CONSTRAINT PK_Advertise PRIMARY KEY (ID)
)

GO
CREATE TABLE Course(
	ID int IDENTITY(1,1) NOT NULL,
	Name nvarchar(255) NOT NULL,
	ThumbImage nvarchar(255) NULL,
	[Description] ntext NULL,
	Content ntext NOT NULL,
	Quantities int NOT NULL,
	StartDate datetime NOT NULL,
	Schedule nvarchar(50) NOT NULL,
	TimeSlot nvarchar(50) NOT NULL,
	IsFrontPage bit NOT NULL,
	[Status] bit NOT NULL,
	CONSTRAINT PK_Course PRIMARY KEY (ID)
)

GO 
CREATE TABLE CheckIn(
	ID int IDENTITY(1,1) NOT NULL,
	FullName nvarchar(255) NOT NULL,
	Phone varchar(50) NOT NULL,
	DateOfBirth datetime NOT NULL,
	Email varchar(255) NOT NULL,
	Company nvarchar(255) NOT NULL,
	Note nvarchar(255) NOT NULL,
	CONSTRAINT PK_CheckIn PRIMARY KEY (ID)
)
GO
CREATE TABLE RegisterCourse(
	ID int IDENTITY(1,1) NOT NULL,
	CheckInID int NOT NULL,
	CourseID int NOT NULL
	CONSTRAINT PK_Register PRIMARY KEY (ID),
	CONSTRAINT FK_Register_Course FOREIGN KEY (CourseID) REFERENCES Course(ID),
	CONSTRAINT FK_Register_CheckIn FOREIGN KEY (CheckInID) REFERENCES CheckIn(ID)
)

GO
CREATE TABLE [Partner](
	ID int IDENTITY(1,1) NOT NULL,
	ImageUrl varchar(MAX) NOT NULL,
	Link varchar(MAX) NOT NULL,
	CONSTRAINT PK_Partner PRIMARY KEY (ID)
)

GO
Create TABLE [FeedBack](
	ID int IDENTITY(1,1) NOT NULL,
	FullName nvarchar(50) NOT NULL,
	Email varchar(255) NOT NULL,
	Content ntext NOT NULL,
	DateSend datetime NOT NULL,
	CONSTRAINT PK_FeedBack PRIMARY KEY(ID)
)
GO
CREATE TABLE [SmtpEmail](
	ID int IDENTITY(1,1) NOT NULL,
	MailFrom nvarchar(255) NOT NULL,
	MailFromPassword nvarchar(255) NOT NULL,
	MailTo nvarchar(255)NOT NULL,
	SmtpClient nvarchar(255) NOT NULL,
	SmtpHost nvarchar(255) NOT NULL,
	SmtpPort int NOT NULL,
	CONSTRAINT PK_SmtpEmail PRIMARY KEY(ID)
)
GO 
INSERT INTO [SmtpEmail]
           ([MailFrom]
           ,[MailFromPassword]
           ,[MailTo]
           ,[SmtpClient]
           ,[SmtpHost]
           ,[SmtpPort])
     VALUES
           ('tuananh81189@gmail.com'
           ,'tuananh8111989'
           ,'tuananh81189@gmail.com'
           ,'127.0.0.1'
           ,'smtp.gmail.com'
           ,'587')
GO
CREATE TABLE Config(
	ID int unique NOT NULL,
	IconUrl varchar(255) NULL,
	ButtonShare ntext NULL,
	Title nvarchar(255) NULL,
	Footer ntext NULL,
	MetaTitle nvarchar(255) NULL,
	MetaDescription ntext NULL,
	MetaKeywords ntext NULL,
	MetaRobots nvarchar(255) NULL,
	MetaAuthor nvarchar(255) NULL,
	CONSTRAINT PK_Config PRIMARY KEY(ID)
)
GO
INSERT INTO [NLLDatabase].[dbo].[Config]
           ([ID]
           ,[IconUrl]
           ,[ButtonShare]
           ,[Title]
           ,[Footer]
           ,[MetaTitle]
           ,[MetaDescription]
           ,[MetaKeywords]
           ,[MetaRobots]
           ,[MetaAuthor])
     VALUES
           (1
           ,'nll'
           ,'nll'
           ,'nll'
           ,'nll'
           ,'nll'
           ,'nll'
           ,'nll'
           ,'nll'
           ,'nll')

GO
CREATE TABLE Ebook(
	ID int IDENTITY(1,1) NOT NULL,
	ImageUrl varchar(MAX) NULL,
	Title nvarchar(255) NOT NULL,
	[Description] nvarchar(MAX) NOT NULL,
	FileUrl varchar(MAX) NOT NULL,
	CONSTRAINT PK_Ebook PRIMARY KEY(ID)
)
CREATE TABLE SLIDE
(
ID INT IDENTITY(1,1) NOT NULL,
ThumbImage nvarchar(MAX)NOT NULL, 
[Description] nvarchar(50)NOT NULL, 
SlideImage nvarchar(MAX)NOT NULL, 
[Status] bit, 
Link varchar(MAX)NOT NULL,
CONSTRAINT PK_slide PRIMARY KEY(ID)
)